<?php
namespace app\admin\controller;
use think\Controller;
use app\admin\controller\Common;
use app\service\orderService;


use app\service\parkingService;
use printing\printing;
use think\Db;
use think\db\Where;
use think\Request;
use think\Response;

use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
class Carvalue extends Common{

    public function carlist(){
		 //根据当前账号查询区域
        $com = $this->pidTokenId();
        $whe=new Where();
        $whe['community_id'] = array('in',implode(',',$com['community_id']));
        //根据账号查询停车场
        $parkinglist = (new orderService())->parkingList($whe);
        $this->assign('parkinglist',$parkinglist);
		 //查询停车场ID
        if(!empty($parkinglist)){
            foreach($parkinglist as $park){
                $parkingNo[] = $park['parkingNo'];
            }
            //将数组转换成字符串
            $parkingNo = implode(',',$parkingNo);
        }else{
            $parkingNo = null;
        }
        $community_id = $com['community_id'];
        //搜索
        $data = request()->get();
        // dump($data);
        if (!empty($data)){
            if(!empty($data['parkingNo'])){
                $where['parkingNo'] = $data['parkingNo'];//停车场名称
            }else{
                $where['parkingNo'] = "";
            }
            if(!empty($data['car_number'])){
               $where['car_number'] = array('like','%'.$data['car_number'].'%');//车牌号码//车牌号码
            }else{
                $where['car_number'] = "";
            }
			
            if(!empty($data['owner_phone'])){
                $where['owner_phone'] = $data['owner_phone'];//车主电话
            }else{
                $where['owner_phone'] = "";
            }
			if(!empty($data['owner_name'])){
                $where['owner_name'] = $data['owner_name'];//车主电话
            }else{
                $where['owner_name'] = "";
            }
            
            $this->assign('where',$where);   
            $where = array_filter($where);
        }else{
            $where=array();
        }
        
        $export = json_encode($where);
        $export = str_replace("\"","|",$export);
        $this->assign('export',$export);
        $page = request()->has('page') ? request()->get('page') : 1;
        //多条件查询地库车位信息
        $carlist = Db::name('car_value')->where('parkingNo', 'in', $parkingNo)->where($where)->order('id desc')->paginate(100, false, [
            'page'  =>  $page,
            'query' =>  $where
        ]);
        $number = Db::name('car_value')->where('parkingNo', 'in', $parkingNo)->where($where)->count();
        $this->assign('number',$number);
		
        $this->assign('carlist',$carlist);
		$this->assign('list',$carlist);
        foreach ($data as $k => $v) {
            $this->assign($k, $v);
        }
        return $this->fetch('carlist');
	}
	
	public function caradd(){
		//根据当前账号查询区域
        $com = $this->pidTokenId();
        $whe=new Where();
        $whe['community_id'] = array('in',implode(',',$com['community_id']));
        $parkinglist = (new orderService())->parkingList($whe);
        $this->assign('parkinglist',$parkinglist);

        if(request()->isPost()){
            //获取表单数据
            $data = request()->post();
			$data["car_number"]=trim($data["car_number"]);
            $data['create_time'] = time();
			$data["balance"]=$data["totalmoney"];
			$parkinglotinfo=Db::table("parkinglotinfo")->where("parkingNo",$data["parkingNo"])->find();
			//查询该车辆是否已经添加
			$carinfo=Db::table("car_value")->where("parkingNo",$data["parkingNo"])->where("car_number",$data["car_number"])->find();
			if(!empty($carinfo))
			{
				$this->error('该车辆已经添加',url('caradd'));
			}
			
			
            //插入数据到数据库
            $res = Db::table("car_value")->insert($data);
           
            if($res)
			{
				$car_value_recharge["parkingNo"]=$data["parkingNo"];
				$car_value_recharge["car_value_id"]=Db::name('car_value')->getLastInsID();
				$car_value_recharge["parkingName"]=(new orderService())->parkingIdName($data["parkingNo"]);;
				$car_value_recharge["car_number"]=$data['car_number'];
				$car_value_recharge["money"]=$data["balance"];
				$car_value_recharge["create_time"]=$data['create_time'];
				$car_value_recharge["remark"]="首次充值";
				$car_value_recharge["operator"]=session("name");
				Db::table("car_value_recharge")->insert($car_value_recharge);
                $this->success('车辆添加成功',url('caradd'));
            }else
			{
                $this->error('车辆添加失败',url('caradd'));
            }
        }else{
			 return $this->fetch('');
		}
       
	}
	
	//储值车充值
	public function recharge()
	{
		if(request()->isPost())
		{
			$data=request()->post();
			//echo "<pre>";var_dump(request()->post());die;
			$res=Db::table('car_value')
    				->where('id',$data["id"])
    				->inc('totalmoney',$data["money"])
    				->inc('balance',$data["money"])
    				->update();
			
			if($res){
			    $car_value_recharge["parkingNo"]=$data["parkingNo"];
    			$car_value_recharge["car_value_id"]=$data["id"];
    			$car_value_recharge["parkingName"]=$data["parkingName"];
    			$car_value_recharge["car_number"]=$data["car_number"];
    			$car_value_recharge["money"]=$data["money"];
    			$car_value_recharge["create_time"]=time();
    			$car_value_recharge["remark"]=$data["remark"];
    			$car_value_recharge["operator"]=session("name");
    			Db::table("car_value_recharge")->insert($car_value_recharge);
    			$this->success('充值成功');
			}else{
			    $this->error('充值失败');
			}
			
			
				
				
			
		}else
		{
			$id=request()->get("id");
			$info=Db::table("car_value")
					->alias('a')
					->join("parkinglotinfo b","a.parkingNo=b.parkingNo")
					->where("a.id",$id)
					->field("a.*,b.parkingName")
					->find();
			if(empty($info))
			{
				$this->error("该储值车不存在");
			}
			$this->assign("info",$info);
			return $this->fetch();
		}
		
	}
	
	//充值记录
    public function rechargeList()
    {
        //根据当前账号查询区域
        $com = $this->pidTokenId();
        $whe=new Where();
        $whe['community_id'] = array('in',implode(',',$com['community_id']));
        $parkinglist = (new orderService())->parkingList($whe);
        $this->assign('parkinglist',$parkinglist);
        //查询停车场ID
        if(!empty($parkinglist)){
            foreach($parkinglist as $park){
                $parkingNo[] = $park['parkingNo'];
            }
            //将数组转换成字符串
            $parkingNo = implode(',',$parkingNo);
        }else{
            $parkingNo = null;
        }
        $data = request()->get();
        //dump($data);die;
        if(!empty($data)){
            if(!empty($data['parkingNo'])){
                $where['parkingNo'] = $data['parkingNo'];
				$parkingNo=$data['parkingNo'];
            }else{
                $where['parkingNo'] = '';
            }
            if(!empty($data['car_number'])){
                $where['car_number'] = array("like",'%'.$data['car_number'].'%');
            }else{
                $where['car_number'] = '';
            }
            $this->assign('where',$where);
            $where = array_filter($where);
        }else{
            $where = array();
        }
        $export = $where;
		
        $export = json_encode($export);
        $export = str_replace("\"","|",$export);
        $this->assign('export',$export);
        //查询卷信息
        $carList = Db::table("car_value_recharge")
							->where("parkingNo","in",$parkingNo)
							->where($where)
							->where("status","1")
							->order('id desc')
							->paginate(20);
       
        $number = Db::name('car_value_recharge')->where("status","1")->where($where)->count();
        $this->assign('number',$number);
        $this->assign('list',$carList);
        foreach ($data as $k => $v) {
            $this->assign($k, $v);
        }
        return $this->fetch();
    }
	public function rechargedump(){
		//获取账单数据
        $export = request()->param('exportid');
		
        $export = str_replace("|","\"",$export);
        //根据当前账号查询区域
        $com = (new Common())->pidTokenId();
        $whe=new Where();
        $whe['community_id'] = array('in',implode(',',$com['community_id']));
        //根据账号查询停车场
        $parkinglist = (new orderService())->parkingList($whe);
		 //查询停车场ID
        if(!empty($parkinglist)){
            foreach($parkinglist as $park){
                $parkingNo[] = $park['parkingNo'];
            }
            //将数组转换成字符串
            $parkingNo = implode(',',$parkingNo);
        }else{
            $parkingNo = null;
        }
		
        $where = json_decode($export,true);
		
		//多条件查询储值车充值记录信息
		$where["status"]=1;
        $rechargelist = Db::name('car_value_recharge')->where('parkingNo', 'in', $parkingNo)->where($where)->order('id desc')->select();
		$num=Db::name('car_value_recharge')->where('parkingNo', 'in', $parkingNo)->where($where)->count();
		if($num=="0"){
			return  "无数据";
		}
		//引入PHPExcel类
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        //设置宽度为true,不然太窄了
        $newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        //设置表头
        $objSheet->setTitle('储值车订单'); 
        $objSheet->setCellValue('A1', '停车场名称')
            ->setCellValue('B1', '车牌号')
            ->setCellValue('C1', '充值金额')
            ->setCellValue('D1', '备注')
            ->setCellValue('E1', '充值时间');
        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
		for ($i = 2; $i <= $num+1; $i++) {
            $objSheet->setCellValue('A' . $i, $rechargelist[$i-2]["parkingName"]);
            $objSheet->setCellValue('B' . $i, $rechargelist[$i-2]["car_number"]);
            $objSheet->setCellValue('C' . $i, $rechargelist[$i-2]["money"]);
            $objSheet->setCellValue('D' . $i, $rechargelist[$i-2]["remark"]);
			$ctime=date('Y-m-d H:i:s',$rechargelist[$i-2]["create_time"]);
            $objSheet->setCellValue('E' . $i, $ctime);
        }
        $this->downloadExcel($newExcel, "储值车订单", 'Xls'); //表示在$path路径下面生成demo.xlsx文件
	}
	/*public function caredit(){
		//根据当前账号查询区域
        $com = $this->pidTokenId();
        $whe['community_id'] = array('in',implode(',',$com['community_id']));
        $parkinglist = (new packageService())->parkingList($whe);
        $this->assign('parkinglist',$parkinglist);

        if(request()->isPost()){
            //获取表单数据
            $data = request()->post();
			$data["car_number"]=trim($data["car_number"]);
			$carownerinfo=Db::table("car_owner_welfare")->where("id",$data["id"])->find();
			//查询该车辆是否已经添加
			$carinfo=Db::table("car_owner_welfare")->where("out_parking_id",$carownerinfo["out_parking_id"])->where("car_number",$data["car_number"])->where("id","neq",$data["id"])->find();
			if(!empty($carinfo))
			{
				$this->error('该车辆已经添加',url('caredit'));
			}
			
			
            //插入数据到数据库
            $res = Db::table("car_owner_welfare")->update($data);
           
            if($res)
			{
				$carownerinfo["operate_name"]=session("name");
				$carownerinfo["action"]="1";
				$carownerinfo["operate_time"]=time();
				Db::table("car_owner_welfare_operate")->insert($carownerinfo);
                $this->success('车辆修改成功',url('caradd'));
            }else
			{
                $this->error('车辆修改失败',url('caradd'));
            }
        }else{
			$id=request()->get("id");
			$carinfo=Db::table("car_owner_welfare")->where("id",$id)->find();
			$this->assign("carinfo",$carinfo);
			return $this->fetch('');
		}
       
	}*/
	
	
	public function cardel(){
		$id=request()->get("id");
		$carinfo=Db::table("car_value")->where('id',$id)->find();
		
		$carinfo["operator"]=session("name");
		
		Db::table("car_value_del")->insert($carinfo);
		$bool= Db::name('car_value')->delete($id);
		if($bool){
			Db::table("car_value_recharge")->where("car_value_id",$carinfo["id"])->update(["status"=>"2"]);
			$this->success('删除成功',url('carlist'));
		}else{
			$this->error('删除失败',url('carlist'));
		}
	}
	
	public function excel()
	{
		if(request()->isPOST()){
            //上传excel文件
            $file = request()->file('myfile');
            //移到/public/excel/下
            $info = $file->move(ROOT_PATH.'public'.DS.'excel');
            //上传文件成功
            if ($info) {
                //引入PHPExcel类
                vendor('PHPExcel.PHPExcel.Reader.Excel2007');
                //获取上传后的文件名
                $fileName = $info->getSaveName();
                //文件路径
                $filePath = dir(getcwd())->path.'\excel\\'.$fileName;
                //判断文件格式，实例化相应的PHPExcel类，读取excel文件
                $extension = strtolower( pathinfo($fileName, PATHINFO_EXTENSION));
                if ($extension =='xlsx' || $extension =='xls') {
                    $objReader = new \PHPExcel_Reader_Excel2007();
                    $objPHPExcel = $objReader ->load($filePath);
                } else if ($extension=='csv') {
                    $objReader = new \PHPExcel_Reader_CSV();
                    //默认输入字符集
                    $objReader->setInputEncoding('GBK');
                    //默认的分隔符
                    $objReader->setDelimiter(',');
                    //载入文件
                    $objPHPExcel = $objReader ->load($filePath);
                }else{
                    $this->success('文件格式错误',url('packageList'));
                }  
                //读取excel文件中的第一个工作表
                $sheet = $objPHPExcel->getSheet(0);
                $allRow = $sheet->getHighestRow();  //取得总行数
                //$allColumn = $sheet->getHighestColumn();  //取得总列数
                //从第三行开始插入，第一行是列名
                for ($j=2; $j <= $allRow; $j++) {
					
					$data["out_parking_id"]=$objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取停车场ID
                    $data["parking_name"] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//停车场名称
					$data['car_number'] = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//车牌号码
					$data['car_number']=trim($data['car_number']);
					$data['balance'] = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();//余额
					
					$data['totalmoney']=$data['balance'];
					$data['address'] = $objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();//地址
					$data['owner_phone'] = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();//手机
					$data['owner_name'] = $objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();//姓名
					
					$data = array_filter($data);
                    $number = count($data);
                    $numbers = 7;
					//echo $number;die;
					if($number >= $numbers){
                       $parkinginfo = (new packageService())->parkingNameId($data["parking_name"]);
						if($parkinginfo["out_parking_id"]!=$data["out_parking_id"])
						{
							echo "第".$j."行停车场id和停车场名称不匹配，导入失败！<br/>";
						}else{
							//查询车牌是否重复
							$res = Db::table("car_value")->where("car_number",$data["car_number"])->where("out_parking_id",$data["out_parking_id"])->find();
							if($res){
								echo "第".$j."车牌重复，导入失败！<br/>";
							}else{
								$data["create_time"]=time();
								$last_id = Db::table("car_value")->insert($data); 
								//判断数据是否插入成功
								if ($last_id) {
									if(isset($data["balance"])){
										$car_value_recharge["out_parking_id"]=$data["out_parking_id"];
										$car_value_recharge["car_value_id"]=Db::name('car_value')->getLastInsID();
										$car_value_recharge["parking_name"]=$data["parking_name"];
										$car_value_recharge["car_number"]=$data['car_number'];
										$car_value_recharge["money"]=$data["balance"];
										$car_value_recharge["create_time"]=$data['create_time'];
										$car_value_recharge["remark"]="首次充值";
										$car_value_recharge["operator"]=session("name");
										Db::table("car_value_recharge")->insert($car_value_recharge);
									}
									
									echo "第".$j."行导入成功，<br/>";
								}else{
									echo "第".$j."行导入失败！<br/>";
								} 
							}
						}
                        
                    }else{
                        echo "第".$j."行数据不完整，导入失败！<br/>";
                    }	
                    
                }
				echo "导入结束";
            }else{
                $this->success('上传文件失败',url('excel'));
            }
        }else{
            return $this->fetch('excel');
        }
	}
	
	public function orderinfo()
	{
		$com = $this->pidTokenId();
		$id=request()->get("id");
		$carinfo=Db::table("car_owner_welfare")->where("id",$id)->find();
		$data = request()->get();
		//var_dump($data);
        if (!empty($data)){
			if(!empty($data['in_year'])){
                $where['in_year'] = $data['in_year'];
            }else{
                $where['in_year'] = "";
            }
            if(!empty($data['in_month'])){
                $where['in_month'] = $data['in_month'];
            }else{
                $where['in_month'] = "";
            }
            $this->assign('where',$where);   
            $where = array_filter($where);
        }else{
            $where=array();
        }
		$where["out_parking_id"]=$carinfo["out_parking_id"];
		$where["car_number"]=$carinfo["car_number"];
		$where["pstatus"]="1";
		//var_dump($where);
		$page = request()->has('page') ? request()->get('page') : 1;
		//查询该车的停车记录
		$orderwelfare=Db::table("car_order_welfare")
							->where($where)
							->order("id desc")
							->paginate(100, false, [
								'page'  =>  $page,
								'query' =>  $where
							]);
		$feetime=Db::table("car_order_welfare")
							->where($where)
							->sum("fee_duration");
		$this->assign("feetime",$feetime);
		$this->assign("orderwelfare",$orderwelfare);
		foreach ($data as $k => $v) {
            $this->assign($k, $v);
        }
        $this->assign("id",$id);
		return $this->fetch();
	}
	
	public function carlistdump(){
		//获取账单数据
        $export = request()->param('exportid');
        $export = str_replace("|","\"",$export);
        //根据当前账号查询区域
        $com = (new Common())->pidTokenId();
        $whe['community_id'] = array('in',implode(',',$com['community_id']));
        //根据账号查询停车场
        $parkinglist = (new orderService())->parkingList($whe);
       
        //查询停车场ID
        if(!empty($parkinglist)){
            foreach($parkinglist as $park){
                $out_parking_id[] = $park['out_parking_id'];
            }
            //将数组转换成字符串
            $out_parking_id = implode(',',$out_parking_id);
        }else{
            $out_parking_id = null;
        }
		
        $where = json_decode($export,true);
		
		//多条件查询储值车信息
        $carlist = Db::name('car_value')->where('out_parking_id', 'in', $out_parking_id)->where($where)->order('id desc')->select();
		$num=Db::name('car_value')->where('out_parking_id', 'in', $out_parking_id)->where($where)->count();
		if($num=="0"){
			return  "无数据";
		}
		 //设置表头
        //引入PHPExcel类
        vendor('PHPExcel.PHPExcel.Reader.Excel2007');
        $objPHPExcel = new \PHPExcel();
        //设置表头
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '停车场名称')
            ->setCellValue('B1', '车牌号')
            ->setCellValue('C1', '门牌号')
            ->setCellValue('D1', '联系人')
            ->setCellValue('E1', '联系电话')
            ->setCellValue('F1', '余额')
			->setCellValue('G1', '每次扣款');
        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
		for ($i = 2; $i <= $num+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $carlist[$i-2]["parking_name"]);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $carlist[$i-2]["car_number"]);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $carlist[$i-2]["address"]);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $carlist[$i-2]["owner_name"]);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $carlist[$i-2]["owner_phone"]);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $carlist[$i-2]["balance"]);
			$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $carlist[$i-2]["price"]);
            
            
        }
        $objPHPExcel->getActiveSheet()->setTitle('储值车列表信息');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        header('Content-Disposition: attachment;filename="储值车列表信息.xls"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007"); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
		
	}
	
	
	function downloadExcel($newExcel, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="
            . $filename . date('Y-m-d') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);

        $objWriter->save('php://output');

        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
//         If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        exit;
    }	
}